﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace QuanLyKho
{
    class ConnectDB
    {
        SqlConnection conn;
       // string sqlconn = @"Data Source=.\SQLEXPRESS;Initial Catalog=QLQA;Integrated Security=True";
        string sqlconn = @"Data Source=.\SQLEXPRESS;Initial Catalog=QLQA;Integrated Security=True";

        // mở kết nối
        public void connect()
        {

            if (conn == null)

                conn = new SqlConnection(sqlconn);

            if (conn.State == ConnectionState.Closed)

                conn.Open();

        }

        // đóng kết nối
        public void disconnect()
        {

            if ((conn != null) && (conn.State == ConnectionState.Open))

                conn.Close();

        }

        // trả về một DataTable .
        public DataTable getDataTable(string sql)
        {

            connect();//ket noi o dayuh, đi vòng vòng, vkl

            SqlDataAdapter da = new SqlDataAdapter(sql, conn);

            DataTable dt = new DataTable();

            da.Fill(dt);

            disconnect();

            return dt;

        }

        // thực thi câu lệnh truy vấn insert,delete,update
        public void ExecuteNonQuery(string sql)
        {
            
            connect();

            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.ExecuteNonQuery();

            disconnect();

        }

        // trả về DataReader
        public SqlDataReader getDataReader(string sql)
        {

            connect();

            SqlCommand com = new SqlCommand(sql, conn);

            SqlDataReader dr = com.ExecuteReader();

            return dr;

        }

        //lay id cuoi cung cung 1 dang ma cd:ab0001;ab0002
        public string GetLastID(string nameTable, string nameSelectColumn)
        {
            string sql = "SELECT TOP 1 * " +
                " FROM " + nameTable +
                " ORDER BY " + nameSelectColumn + " DESC";

            DataView dv = new DataView(getDataTable(sql));

            return dv.Table.Rows[0][nameSelectColumn].ToString(); //dv.Table.Rows[a][b] lay du lieu dong a cot b
        }

        public string GetLastIDNumber(string nameTable, string nameSelectColumn, string machung)//ab4567 -> start=2
        {
            int vt = machung.Count();
            string sql = "SELECT * " +
                " FROM " + nameTable +
                " WHERE " + nameSelectColumn + " LIKE '" + machung + "%'";

            DataTable dt = getDataTable(sql);
            
            int kq =0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                int temp = int.Parse(dt.Rows[i][nameSelectColumn].ToString().Remove(0, vt));
                if (temp > kq)
                {
                    kq = temp;
                }
            }
            return machung + kq.ToString();
        }
    }
}
